package com.neusoft.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.bean.User;
import com.neusoft.dao.UserDao;
import com.neusoft.util.DButil;

public class UserDaoImpl implements UserDao{

	@Override
	public int findUserByNP(User user) {
		DButil db = DButil.getDB();
		Connection conn  = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int id = 0;
		try {
			ps = conn.prepareStatement("select * from webuser where username=? and password=?");
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			rs = ps.executeQuery();
			if(rs.next()){
				id = rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				ps.close();
				db.closeConn(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return id;
	}
	
	public User findUserById(int id){
		DButil db = DButil.getDB();
		Connection conn = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = new User();
		try {
			ps = conn.prepareStatement("select * from webuser where id = ?");
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setPower(rs.getInt("power"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				ps.close();
				db.closeConn(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return user;
	}

	@Override
	public int showUserCount() {
		DButil db = DButil.getDB();
		Connection conn = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = conn.prepareStatement("select count(*) from webuser");
			rs = ps.executeQuery();
			if(rs.next()){
				i=rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				ps.close();
				db.closeConn(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	
	public List<User> showUser(int start,int end) {
		DButil db = DButil.getDB();
		Connection conn = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> userList = new ArrayList<>();
		try {
			ps = conn.prepareStatement("select * from (select ROWNUM rn,w.* from WEBUSER w) where rn BETWEEN ? and ?");
			ps.setInt(1, start);
			ps.setInt(2, end);
			rs = ps.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setPower(rs.getInt("power"));
				userList.add(user);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				ps.close();
				db.closeConn(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return userList;
	}
	
	
	
	
	@Override
	public void addUser(User user) {
		DButil db = DButil.getDB();
		Connection conn = db.getConn();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("insert into webuser values(seq_webuser_id.nextval,?,?,?,?)");
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getEmail());
			ps.setInt(4, user.getPower());
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				ps.close();
				db.closeConn(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}

	@Override
	public void updateUser(User user) {
		DButil db = DButil.getDB();
		Connection conn = db.getConn();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("update webuser set username=?,password=?,email=?,power=? where id=?");
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getEmail());
			ps.setInt(4, user.getPower());
			ps.setInt(5, user.getId());
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				ps.close();
				db.closeConn(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}
	
	public User findUserByName(String username) {
		DButil db = DButil.getDB();
		Connection conn = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = new User();
		try {
			ps = conn.prepareStatement("select * from webuser where username=?");
			ps.setString(1, username);
			rs = ps.executeQuery();
			if(rs.next()){
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setPower(rs.getInt("power"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				ps.close();
				db.closeConn(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return  user;
	}
	
	
	@Override
	public void delUser(int id) {
		DButil db = DButil.getDB();
		Connection conn = db.getConn();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("delete from webuser where id=?");
			ps.setInt(1, id);
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				ps.close();
				db.closeConn(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}
	
	
}
